#!/usr/bin/env bash

mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select day,station_id,trim(product_code) product_code,sum(ordinary_discount) ordinary_discount from
          (
          select substr(A.transaction_date,1,10) day,A.station_id,'非油' product_code,B.item_value ordinary_discount
          from
          transactions as A
          JOIN transaction_items_mop AS B
          join transaction_items_notoil as Y
          on A.transaction_id = B.transaction_id and A.station_id = B.station_id and B.item_description='优惠券'
          and A.transaction_id=Y.transaction_id and A.station_id=Y.station_id
          where substr(A.transaction_date,1,10)>='2018-12'
          union all
          select substr(A.transaction_date,1,10) day,A.station_id,Y.product_code,B.item_value  ordinary_discount
          from
          transactions   as A
          JOIN transaction_items_mop AS B
          join transaction_items_oil as Y
          on A.transaction_id = B.transaction_id and A.station_id = B.station_id and B.item_description='优惠券'
          and A.transaction_id=Y.transaction_id and A.station_id=Y.station_id
          where substr(A.transaction_date,1,10)>='2018-12'  
          ) tmp group by day,station_id,trim(product_code);
EOF